Retail Analytics Case Study

Table of contents

• Libraries Used Datasets and Tasks
• Challenge
  • Pump Up the Data: Validation Pit Stop
  • State by State Gross Profit Trail
  • Site Attributes - Geospatial Distribution of Stores
  • Stats for Nerds: “Fueling” Insights, One Number at a Time
  • Charting the Course: Recommendations

Libraries Used Datasets and Tasks

pacman:: p_load(readr, readxl, ggplot2, dplyr, tidyverse, leaflet, pander, DT, leaflet.extras, scales)

file_path <- "Maverik_Interview_Challenge_Data.xlsx"

# Sheet 1 data
store_location <- read_excel(file_path, sheet = 1) 
# Sheet 2 data
metrics_data <- read_excel(file_path, sheet = 2)

This project was completed as part of a take-home case study for a data analyst interview at a regional convenience retail brand. The goal was to explore store-level performance and geospatial patterns using sample data provided in the assessment.

Note: This work is based on publicly shareable data provided for evaluation purposes only. All insights and interpretations are my own, and no proprietary or confidential information is included in this project. Company names and specific identifiers have been omitted to respect privacy.

Challenge

Performance Analysis: Calculate the combined gross profit store average for each state, including actionable insights or observations.
Visualization: Create an insightful map visualization comparing store attributes (from store location data) and financial metrics (from metrics) of your choice.

Pump Up the Data: Validation Pit Stop

# Pivot Wider
metric <- metrics_data %>%
  pivot_wider(
    names_from = Metric,
    values_from = Value
  )

pander(head(metrics_data))

StoreNumber	Metric	Value
130	FuelGrossProfit	188275
130	InStoreGrossProfit	194941
136	FuelGrossProfit	290636
136	InStoreGrossProfit	234793
156	FuelGrossProfit	201057
156	InStoreGrossProfit	200821

pander(head(metric))

StoreNumber	FuelGrossProfit	InStoreGrossProfit
130	188275	194941
136	290636	234793
156	201057	200821
162	254720	146856
166	275758	199879
168	345998	227923

missing_values_store_location <- store_location[!complete.cases(store_location), ]
missing_values_metrics <- metric[!complete.cases(metric), ]

pander(missing_values_store_location, 
  caption = "Missing Values in Store Location Data")

Missing Values in Store Location Data (continued below)

Store Number	Pump Count	Parking Spots	Street
537	NA	28	1290 S. Wallace Rd.
603	6	NA	303 S Main Street

City	State	Zip	Latitude	Longitude
Salt Lake City	UT	84104	40.74	-111.9
Logan	UT	84321	41.73	-111.8

pander(missing_values_metrics, 
  caption = "Missing Values in Metrics Data")

Missing Values in Metrics Data

StoreNumber	FuelGrossProfit	InStoreGrossProfit
623	NA	333345

Analysis:

• Missing values in Pump Count for store 537 and Parking Spots for store 603 indicate incomplete data for these locations.
• Store 623 lacks FuelGrossProfit, which could skew profit-based analyses.
• Further analysis noted that Store 254 had the wrong coordinates (need to refer back to source - Long. & Lat. probably swapped around).

Workflow:

1. Used complete.cases() to identify rows with missing data.
2. Made use of pivot_widerto transform rows into columns.
3. Segregated missing data into separate tables for analysis and review.

Assumptions:

• Missing values might indicate errors during data collection or unrecorded attributes for new stores.
• Stores with incomplete data were excluded from further analysis to maintain accuracy.

Challenges:

• Deciding whether to exclude or impute missing values for incomplete rows.

State by State Gross Profit Trail

# Joining Datasets
Store_Metrics <- store_location %>%
  inner_join(metric, by = c("Store Number" = "StoreNumber")) %>%
  filter(!`Store Number` %in% c(254, 537, 603, 623))%>%
  mutate(TotalGrossProfit = FuelGrossProfit + InStoreGrossProfit)

state_avg_gross_profit <- Store_Metrics %>%
  group_by(State) %>%
  summarize(
    "Fuel Gross Profit" = paste0("$ ",round(mean(FuelGrossProfit, na.rm = TRUE),2)),
    "InStore Gross Profit" = paste0("$ ",round(mean(InStoreGrossProfit, na.rm = TRUE),2)),
    "Total Gross Profit" = paste0("$ ",round(mean(FuelGrossProfit + InStoreGrossProfit, na.rm = TRUE),2))
  ) %>%
  arrange(desc("Total Gross Profit"))

datatable(state_avg_gross_profit,
                 options = list(pageLength = 7),
                 caption = "Average Gross Profits by State")

Analysis:

• Utah (UT) leads significantly in average gross profit, contributing, driven by higher store density and operational efficiency.
• California (CA) follows, likely due to high-performing stores with optimized attributes.
• Wyoming (WY) and Utah (listed separately) report the lowest average profits.

Workflow:

1. Filtered out invalid store data (e.g., missing values).
2. Aggregated gross profits at the state level using group_by() and summarize().
3. Displayed the result in an interactive table using datatable().

Assumptions:

• Data is accurate and consistently aggregated across states.
• Average gross profit provides meaningful insights for comparing state-level performance.

Challenges:

• Handling states with fewer stores, which might skew average values.

Site Attributes - Geospatial Distribution of Stores

Map_visuals <- Store_Metrics %>%
  mutate(
    popup_info = paste0(
      "<b>Store Number:</b> ", `Store Number`, "<br>",
      "<b>State:</b> ", State, "<br>",
      "<b>Fuel Gross Profit:</b> $", FuelGrossProfit, "<br>",
      "<b>In-Store Gross Profit:</b> $", InStoreGrossProfit, "<br>",
      "<b>Parking Spots:</b> ", `Parking Spots`, "<br>",
      "<b>Pump Count:</b> ", `Pump Count`
    )
  )

leaflet(data = Map_visuals) %>%
  addTiles() %>%
  addCircleMarkers(
    lng = ~Longitude,
    lat = ~Latitude,
    radius = ~TotalGrossProfit / 30000, 
    color = "red",
    popup = ~popup_info
  ) %>%
  addLegend(
    position = "bottomright",
    title = "Gross Profit",
    colors = "red",
    labels = "Total Gross Profit"
  )

Analysis:

• High gross-profit-generating stores are concentrated in urban centers or regions with high traffic flow (e.g., Salt Lake City, UT).
• Geographic clustering of high-performing stores reveals strong regional opportunities.
• Larger marker sizes correlate with infrastructure scale and operational success.

Workflow:

1. Used leaflet to create an interactive map with geospatial data.
2. Added circle markers with popups displaying detailed store metrics.

Assumptions:

• Latitude and longitude data are accurate for mapping.
• Larger markers indicate higher Fuel Gross Profit.

Challenges:

• Balancing marker size for clarity while ensuring interactivity.

Stats for Nerds: “Fueling” Insights, One Number at a Time

How do parking spots and pump counts influence the total gross profit of our stores?

# Building linear model
profit_model <- lm(TotalGrossProfit ~ `Parking Spots` + `Pump Count`, data = Map_visuals)

pander(summary(profit_model))

	Estimate	Std. Error	t value	Pr(>|t|)
(Intercept)	169597	19587	8.659	1.508e-16
Parking Spots	16707	822	20.32	3.673e-62
Pump Count	12480	1178	10.6	4.416e-23

Fitting linear model: TotalGrossProfit ~ Parking Spots + Pump Count

Observations	Residual Std. Error	R^2	Adjusted R^2
372	140708	0.7573	0.756

Analysis:

• Intercept: When both parking spots and pump counts are zero, the base gross profit is approximately $169,597.
  
• Parking Spots Coefficient: Each additional parking spot increases total gross profit by $16,707.
  
• Pump Count Coefficient: Each additional pump adds around $12,480 to the total gross profit.
  
• High t-values and low p-values confirm the predictors’ statistical significance.

model_residuals <- residuals(profit_model)

pander(summary(model_residuals))

Min.	1st Qu.	Median	Mean	3rd Qu.	Max.
-415609	-78556	-7898	2.802e-12	87945	424133

qqnorm(model_residuals, main = "QQ Plot of Residuals")
qqline(model_residuals, col = "red", lty = 2)

Analysis:

• Optimizing infrastructure (e.g., adding pumps or parking spots) can directly enhance profitability.
• The residuals generally follow a normal distribution, as indicated by their alignment with the theoretical quantile line in the QQ plot.
• Some residuals deviate at the tails, suggesting potential outliers or non-linearities in the data.

ggplot(Map_visuals, aes(x = `Parking Spots`, y = `Pump Count`)) +
  geom_point(aes(color = FuelGrossProfit + InStoreGrossProfit), size = 2, alpha = 0.8) +
  scale_color_viridis_c(option = "viridis", name = "Total Profit ($)") +
  geom_smooth(method = "lm", se = FALSE, color = "red", linetype = "dashed") +
  labs(
    title = "Profitability, Parking Spots, and Pump Count",
    x = "Parking Spots",
    y = "Pump Count"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(hjust = 0.5, size = 14, face = "bold"),
    legend.position = "right"
  )

Analysis:

• Optimizing infrastructure (e.g., adding pumps or parking spots) can directly enhance profitability.
• The diminishing returns for very high numbers of pumps/parking spots suggest a limit to infrastructure-driven growth.
• Balancing parking spots and pump counts is crucial for maximizing profitability.

Workflow:

1. Merged store and metrics datasets.
2. Created a Linear model aka stats for nerds using lm().
3. Utilized qqnorm to Validate Model Assumptions.
4. Plotted relationships using ggplot2 with geom_point() and geom_smooth() for the regression line.

Assumptions:

• Positive correlation suggests more infrastructure (pumps and parking spots) leads to higher profitability.

Challenges:

• Outliers could impact the regression line’s accuracy.

Charting the Course: Recommendations

Top Recommendations (High Priority and Impact)

1. Expand in High-Performing States (UT, CA): Focus resources on high-profit regions for maximum ROI.

2. Optimize Parking and Pump Counts: Use the insights to design or renovate stores in key locations to increase customer throughput and profitability.

3. Improve Underperforming States (WY, ID): Analyze underperforming regions to identify bottlenecks and enhance profitability.

Supporting Recommendations (Medium Priority)

4. Leverage Geospatial Insights: Use map visualizations to identify trends and replicate successful store designs.

5. Targeted Marketing Campaigns: Focus on stores with high potential but underwhelming performance to maximize revenue.

6. Investigate Store-Level Performance: Understand outliers where similar infrastructure yields different results (qualitative research).

Long-Term Recommendations (Low Priority or Strategic)

7. Predictive Modeling: Use regression models to guide new store openings or expansions.

8. Sustainability Initiatives: Explore adding eco-friendly features to align with customer trends and future-proof operations.